package com.df.springmvc.bi.rcjg.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.hibernate.SQLQuery;
import org.hibernate.Session;

import com.common.bean.SQLInsertBean;
import com.common.bean.SQLResultBean;
import com.df.springmvc.supper.SupperDao;

/**
 * 读取数据库的操作
 * @author David
 */
public class rcjgDao extends SupperDao{
	private String message;

	/**
	 * 获取生产报表信息列表
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HashMap<String, Object>> getGetsc(HashMap<String, String> paramMap) throws Exception {
		// TODO Auto-generated method stub
		String sqls1="";
		String StartTime = paramMap.get("StartTime");
		if(StartTime != null && !"".equals(StartTime)){
			String[] StartTimes=StartTime.split("-");
			String StartTimesYear= StartTimes[0];
			String StartTimesMonth= StartTimes[1];
			sqls1=sqls1+"  AND T.YEAR='"+StartTimesYear+"' AND T.MONTH='"+StartTimesMonth+"' ";
			//sqls1=sqls1+" AND  XZSP_SPJYXKZ.TIME_ID>0";
		}
		String sql="SELECT '检查生产主体次数' AS xmname,'家次' AS jldw,sum(case when RCJG_SPSC.ENTP_TYPE ='007' then RCJG_SPSC.JCSCZC else 0 end ) as spsc,sum(case when RCJG_SPSC.ENTP_TYPE ='007001' then RCJG_SPSC.JCSCZC else 0 end ) as sptjjsc,sum(case when RCJG_SPSC.ENTP_TYPE ='018' then RCJG_SPSC.JCSCZC else 0 end ) as spjgxzf " +
				"FROM RCJG_SPSC LEFT JOIN TIME T ON T.TIME_ID = RCJG_SPSC.TIME_ID WHERE 1=1  " +sqls1 +
				"UNION ALL  " +
				"SELECT '出动检查人次' AS xmname,'人次' AS jldw,sum(case when RCJG_SPSC.ENTP_TYPE ='007' then RCJG_SPSC.CDJCRC else 0 end ) as spsc,sum(case when RCJG_SPSC.ENTP_TYPE ='007001' then RCJG_SPSC.CDJCRC else 0 end ) as sptjjsc,sum(case when RCJG_SPSC.ENTP_TYPE ='018' then RCJG_SPSC.CDJCRC else 0 end ) as spjgxzf   " +
				"FROM RCJG_SPSC LEFT JOIN TIME T ON T.TIME_ID = RCJG_SPSC.TIME_ID WHERE 1=1   " +sqls1 +
				"UNION ALL " +
				"SELECT '违法违规生产主体数' AS xmname,'家次' AS jldw,sum(case when RCJG_SPSC.ENTP_TYPE ='007' then RCJG_SPSC.WFWGSC else 0 end ) as spsc,sum(case when RCJG_SPSC.ENTP_TYPE ='007001' then RCJG_SPSC.WFWGSC else 0 end ) as sptjjsc,sum(case when RCJG_SPSC.ENTP_TYPE ='018' then RCJG_SPSC.WFWGSC else 0 end ) as spjgxzf   " +
				"FROM RCJG_SPSC LEFT JOIN TIME T ON T.TIME_ID = RCJG_SPSC.TIME_ID WHERE 1=1  " +sqls1 +
				"UNION ALL " +
				"SELECT '违法违规问题数' AS xmname,'个' AS jldw,sum(case when RCJG_SPSC.ENTP_TYPE ='007' then RCJG_SPSC.WFWGWT else 0 end ) as spsc,sum(case when RCJG_SPSC.ENTP_TYPE ='007001' then RCJG_SPSC.WFWGWT else 0 end ) as sptjjsc,sum(case when RCJG_SPSC.ENTP_TYPE ='018' then RCJG_SPSC.WFWGWT else 0 end ) as spjgxzf  " +
				"FROM RCJG_SPSC LEFT JOIN TIME T ON T.TIME_ID = RCJG_SPSC.TIME_ID WHERE 1=1  " +sqls1 +
				"UNION ALL " +
				"SELECT '完成整改生产主体数' AS xmname,'家次' AS jldw,sum(case when RCJG_SPSC.ENTP_TYPE ='007' then RCJG_SPSC.WCZGSCZT else 0 end ) as spsc,sum(case when RCJG_SPSC.ENTP_TYPE ='007001' then RCJG_SPSC.WCZGSCZT else 0 end ) as sptjjsc,sum(case when RCJG_SPSC.ENTP_TYPE ='018' then RCJG_SPSC.WCZGSCZT else 0 end ) as spjgxzf   " +
				"FROM RCJG_SPSC LEFT JOIN TIME T ON T.TIME_ID = RCJG_SPSC.TIME_ID WHERE 1=1 "+sqls1  ;
				/*"UNION ALL " +
				"SELECT '移交稽查部门立案' AS xmname,'家次' AS jldw,sum(case when RCJG_SPSC.ENTP_TYPE ='007' then RCJG_SPSC.YJJCBMLA else 0 end ) as spsc,sum(case when RCJG_SPSC.ENTP_TYPE ='007001' then RCJG_SPSC.YJJCBMLA else 0 end ) as sptjjsc,sum(case when RCJG_SPSC.ENTP_TYPE ='007002' then RCJG_SPSC.YJJCBMLA else 0 end ) as spjgxzf  " +
				"FROM RCJG_SPSC LEFT JOIN TIME T ON T.TIME_ID = RCJG_SPSC.TIME_ID WHERE 1=1 ";*/
		ArrayList<HashMap<String, Object>> scinfo=querySqlToSQLResultBean(sql).getMapList();
		return scinfo;
	}
	
	/**
	 * 获取经营报表信息列表
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HashMap<String, Object>> getGetjy1(HashMap<String, String> paramMap) throws Exception {
		// TODO Auto-generated method stub
		/*(保证原则性：一个区域同一时间只有一条统计汇总数据),为了避免计算覆盖率导致汇总数据重复，覆盖率自己算*/
		String sqls1="";
		String StartTime = paramMap.get("StartTime");
		if(StartTime != null && !"".equals(StartTime)){
			String[] StartTimes=StartTime.split("-");
			String StartTimesYear= StartTimes[0];
			String StartTimesMonth= StartTimes[1];
			sqls1=sqls1+"  AND T.YEAR='"+StartTimesYear+"' AND T.MONTH='"+StartTimesMonth+"' ";
			//sqls1=sqls1+" AND  XZSP_SPJYXKZ.TIME_ID>0";
		}
		String sql="SELECT '检查经营主体次数' AS xmname,'家次' AS jldw,sum(case when RCJG_SPJY.ENTP_TYPE ='005002' then RCJG_SPJY.JCJYZC else 0 end ) AS XS,sum(case when RCJG_SPJY.ENTP_TYPE ='005001' then RCJG_SPJY.JCJYZC else 0 end ) AS CY"
                  +" FROM RCJG_SPJY LEFT JOIN TIME T ON T.TIME_ID = RCJG_SPJY.TIME_ID WHERE 1=1  "+sqls1
                  +" UNION ALL "
                  +"SELECT '出动检查人次' AS xmname,'人次' AS jldw,sum(case when RCJG_SPJY.ENTP_TYPE ='005002' then RCJG_SPJY.CDJCRC else 0 end ) AS XS,sum(case when RCJG_SPJY.ENTP_TYPE ='005001' then RCJG_SPJY.CDJCRC else 0 end ) AS CY"
                  +" FROM RCJG_SPJY LEFT JOIN TIME T ON T.TIME_ID = RCJG_SPJY.TIME_ID WHERE 1=1  "+sqls1
                  +" UNION ALL "
                  +"SELECT '抽检经营主体数' AS xmname,'家次' AS jldw,sum(case when RCJG_SPJY.ENTP_TYPE ='005002' then RCJG_SPJY.KJJYZT else 0 end ) AS XS,sum(case when RCJG_SPJY.ENTP_TYPE ='005001' then RCJG_SPJY.KJJYZT else 0 end ) AS CY"
                  +" FROM RCJG_SPJY LEFT JOIN TIME T ON T.TIME_ID = RCJG_SPJY.TIME_ID WHERE 1=1  "+sqls1
                  +" UNION ALL "
                  +"SELECT '抽检食品批次数' AS xmname,'批次' AS jldw,sum(case when RCJG_SPJY.ENTP_TYPE ='005002' then RCJG_SPJY.KJSPPCS else 0 end ) AS XS,sum(case when RCJG_SPJY.ENTP_TYPE ='005001' then RCJG_SPJY.KJSPPCS else 0 end ) AS CY"
                  +" FROM RCJG_SPJY LEFT JOIN TIME T ON T.TIME_ID = RCJG_SPJY.TIME_ID WHERE 1=1  "+sqls1
                  +" UNION ALL "
                  +"SELECT '抽检不合格食品批次数' AS xmname,'批次' AS jldw,sum(case when RCJG_SPJY.ENTP_TYPE ='005002' then RCJG_SPJY.KJBHGSPPCS else 0 end ) AS XS,sum(case when RCJG_SPJY.ENTP_TYPE ='005001' then RCJG_SPJY.KJBHGSPPCS else 0 end ) AS CY"
                  +" FROM RCJG_SPJY LEFT JOIN TIME T ON T.TIME_ID = RCJG_SPJY.TIME_ID WHERE 1=1  "+sqls1
                  +" UNION ALL "
                  +"SELECT '发现违法违规经营主体数' AS xmname,'家次' AS jldw,sum(case when RCJG_SPJY.ENTP_TYPE ='005002' then RCJG_SPJY.WFWGJY else 0 end ) AS XS,sum(case when RCJG_SPJY.ENTP_TYPE ='005001' then RCJG_SPJY.WFWGJY else 0 end ) AS CY"
                  +" FROM RCJG_SPJY LEFT JOIN TIME T ON T.TIME_ID = RCJG_SPJY.TIME_ID WHERE 1=1  "+sqls1
                  +" UNION ALL "
                  +"SELECT '发现违法违规问题数' AS xmname,'个' AS jldw,sum(case when RCJG_SPJY.ENTP_TYPE ='005002' then RCJG_SPJY.WFWGWT else 0 end ) AS XS,sum(case when RCJG_SPJY.ENTP_TYPE ='005001' then RCJG_SPJY.WFWGWT else 0 end ) AS CY"
                  +" FROM RCJG_SPJY LEFT JOIN TIME T ON T.TIME_ID = RCJG_SPJY.TIME_ID WHERE 1=1  "+sqls1
                  +" UNION ALL "
                  +"SELECT '完成整改经营主体数' AS xmname,'家次' AS jldw,sum(case when RCJG_SPJY.ENTP_TYPE ='005002' then RCJG_SPJY.WCZGSCZT else 0 end ) AS XS,sum(case when RCJG_SPJY.ENTP_TYPE ='005001' then RCJG_SPJY.WCZGSCZT else 0 end ) AS CY"
                  +" FROM RCJG_SPJY LEFT JOIN TIME T ON T.TIME_ID = RCJG_SPJY.TIME_ID WHERE 1=1  "+sqls1
                  +" UNION ALL "
                  +"SELECT '名厨亮灶实施户数' AS xmname,'户' AS jldw,sum(case when RCJG_SPJY.ENTP_TYPE ='005002' then RCJG_SPJY.MLLZSSHS else 0 end ) AS XS,sum(case when RCJG_SPJY.ENTP_TYPE ='005001'   then RCJG_SPJY.MLLZSSHS else 0 end ) AS CY"
                  +" FROM RCJG_SPJY LEFT JOIN TIME T ON T.TIME_ID = RCJG_SPJY.TIME_ID WHERE 1=1  "+sqls1
                  +" UNION ALL "
                  +"SELECT '量化分级评定总数' AS xmname,'户' AS jldw,sum(case when RCJG_SPJY.ENTP_TYPE ='005002' then RCJG_SPJY.JYZTS else 0 end ) AS XS,sum(case when RCJG_SPJY.ENTP_TYPE ='005001' then RCJG_SPJY.JYZTS else 0 end ) AS CY"
                  +" FROM RCJG_SPJY LEFT JOIN LHFJPD ON  RCJG_SPJY.LHFJPD_ID=LHFJPD.LHFJPD_ID LEFT JOIN TIME T ON T.TIME_ID = RCJG_SPJY.TIME_ID WHERE 1=1 "+sqls1
                  +" UNION ALL "
                  +" SELECT LHFJPD.LHFJPD_NAME AS xmname,'户' AS jldw,sum(case when RCJG_SPJY.ENTP_TYPE ='005002' then RCJG_SPJY.JYZTS else 0 end ) AS XS,sum(case when RCJG_SPJY.ENTP_TYPE ='005001' then RCJG_SPJY.JYZTS else 0 end ) AS CY"
                  +" FROM RCJG_SPJY LEFT JOIN LHFJPD ON  RCJG_SPJY.LHFJPD_ID=LHFJPD.LHFJPD_ID  LEFT JOIN TIME T ON T.TIME_ID = RCJG_SPJY.TIME_ID WHERE 1=1 "+sqls1
                  +" GROUP BY LHFJPD.LHFJPD_NAME";
		return querySqlToSQLResultBean(sql).getMapList();
	}
	
	/**
	 * 获取经覆盖率报表信息列表
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HashMap<String, Object>> getGetjy() throws Exception {
		// TODO Auto-generated method stub
		/*(保证原则性：一个区域同一时间只有一条统计汇总数据),为了避免计算覆盖率导致汇总数据重复，覆盖率自己算*/
		String sql="SELECT APP_CITY_UNIT.CITY_NAME AS 区域,"
                  +"sum(RCJG_SPJY.FWJYTGZ) AS 服务经营提供者数,sum(RCJG_SPJY.JCFGS) AS 检查覆盖数"
                  +" FROM RCJG_SPJY,APP_CITY_UNIT"
                  +" WHERE RCJG_SPJY.TIME_ID>0 AND RCJG_SPJY.CITY_ID=APP_CITY_UNIT.CITY_ID"
                  +" GROUP BY APP_CITY_UNIT.CITY_NAME"
                  +" UNION ALL "
                  +"SELECT '总计' AS 总计,sum(RCJG_SPJY.FWJYTGZ) AS 服务经营提供者数,sum(RCJG_SPJY.JCFGS) AS 检查覆盖数"
                  +""
                  +" FROM RCJG_SPJY,APP_CITY_UNIT"
                  +" WHERE RCJG_SPJY.TIME_ID>0 AND RCJG_SPJY.CITY_ID=APP_CITY_UNIT.CITY_ID";
		return querySqlToSQLResultBean(sql).getMapList();
	}
	/**
	 * 
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HashMap<String, Object>> getinfo() throws Exception {
		// TODO Auto-generated method stub
		ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
		String sql = "select roleCode,roleName from s_role_info where 1=1 and status=1 ";
		SQLResultBean resultBean = querySqlToSQLResultBean(sql, new Object[]{});
		ArrayList<HashMap<String, Object>> mapList = resultBean.getMapList();
		for(int i = 0 ; i < mapList.size() ; i++){
			list.add(mapList.get(i));
		}
		return list;
	}
	
}
